﻿using ICSharpCode.SharpZipLib.Core;
using ICSharpCode.SharpZipLib.Zip;
using Newtonsoft.Json;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web;
using System.Web.Mvc;
using DHQGHN.PM2.Models;

namespace DHQGHN.PM2.Controllers
{

    //Quyvd
    public class printloginExcel
    {
        public string HOUSEHOLD_NAME { get; set; }
        public string AREA_ID_NAME { get; set; }
        public string ADDRESS_ID_NAME { get; set; }
        public int? NUM_OF_HH_MEMBER { get; set; }
        public string HH_HEAD_MOBILE { get; set; }
        public string HH_HEAD_EMAIL { get; set; }
        public string LOGINCODE { get; set; }
    }

    /// <summary>
    /// nguoi tao: tribh
    /// muc dich: obj search
    /// </summary>
    public class SearchPrintLoginCodeModel : SearchModelBase
    {
        [Display(Name = "Địa bàn")]
        public string AreaId { get; set; }
        public string CommuneId { get; set; }
        public string HouseHoldName { get; set; }
        public int? HouseNumber { get; set; }
        [Display(Name = "Trạng thái in")]
        public string StatusPrint { get; set; }
        [Display(Name = "Trạng thái tạo mã")]
        public string StatusGenerateCode { get; set; }
    }
    [Authorize]
    public class PrintLoginCodeController : BaseController
    {
        log4net.ILog logger = log4net.LogManager.GetLogger(typeof(PrintHouseHoldController));

        public ActionResult Index(int? HouseNumber, string AreaId, string HouseHoldName, string StatusGenerateCode, string StatusPrint,
            int? page = 1, int currentPage = 1, int PageSize = 10, int numberOfPages = 0, int tr = 0)
        {
            try
            {

                #region
                SearchPrintLoginCodeModel search = new SearchPrintLoginCodeModel();
                string strAdminCode = MyUser.AdminCode;
                search.CommuneId = strAdminCode;
                search.PageIndex = page;
                search.PageSize = PageSize;
                search.AreaId = AreaId;
                search.StatusGenerateCode = StatusGenerateCode;
                search.StatusPrint = StatusPrint;
                search.HouseHoldName = HouseHoldName;
                search.HouseNumber = HouseNumber;
                //tribh 
                ViewData["StatusGenerateCode"] = StatusGenerateCode;
                ViewData["HouseNumber"] = HouseNumber;
                ViewData["StatusPrint"] = StatusPrint;
                ViewData["HouseHoldName"] = HouseHoldName;
                ViewData["areaId"] = AreaId;
                //if (ViewData["LIST_AREA"] != null)
                //{

                #endregion

                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[105] = "active";
                ViewBag.ActiveMenus[115] = "active";

                if (ViewBag.LIST_AREA == null)
                {
                    List<AreaModel> areaList = new List<AreaModel>();
                    using (WebClient webClient = new WebClient())
                    {
                        Helper.SetHeaderInfo(webClient, "GetByAdminCode");
                        webClient.Encoding = Encoding.UTF8;
                        List<SelectListItem> lstItems = new List<SelectListItem>();
                        try
                        {
                            string dwml = webClient.DownloadString(areaServiceUrl + "GetByAdminCode/" + strAdminCode);
                            areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                        }
                        catch (WebException wex)
                        {
                            logger.Error(wex.ToString());
                            throw;
                        }
                        foreach (var area in areaList)
                        {
                            if (area != null)
                                lstItems.Add(new SelectListItem
                                {
                                    Value = area.AREA_ID,
                                    Text = area.AREA_NAME
                                });
                        }
                        ViewBag.LIST_AREA = lstItems;
                        //if()
                        if (string.IsNullOrEmpty(search.AreaId))
                        {
                            search.AreaId = lstItems.First().Value;
                        }
                        //phunx1, 20150514: pass url Service len view
                        //ViewBag.ServiceUrl = serviceUrl;

                    }
                }

                List<HouseHoldModel> lst = Search(search);
                if (lst.Count() > 0)
                {
                    tr = lst[0].RECODE_COUNT;
                    ViewData["searchResult"] = lst;
                }
                else
                {
                    ViewData["searchResult"] = null;
                }
                //}
                numberOfPages = (int)(tr + PageSize - 1) / PageSize;
                ViewData["pageSize"] = PageSize;
                return View(search);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Index(SearchPrintLoginCodeModel search)
        {
            try
            {
                if (search == null)
                {
                    return View();
                }
                search.PageIndex = search.PageIndex ?? 1;

                List<HouseHoldModel> lst = Search(search);

                return PartialView("_PrintLoginCodeSearchResultPartial", lst);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return Content("Có lỗi xảy ra trong quá trình xử lý dữ liệu: " + ex.Message + ".", "text/html", UTF8Encoding.UTF8);
                throw;
            }
        }

        private List<HouseHoldModel> Search(SearchPrintLoginCodeModel search)
        {
            try
            {
                List<HouseHoldModel> lstHouseHold = new List<HouseHoldModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "SearchPrintCodeHouseHold");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);
                    string jsonResult = wc.UploadString(string.Format("{0}{1}/{2}", serviceUrl, "OnlineService.svc", "SearchPrintCodeHouseHold"), "POST", json);
                    lstHouseHold = JsonConvert.DeserializeObjectAsync<List<HouseHoldModel>>(jsonResult).Result;
                }
                return lstHouseHold;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public FileResult ExportExcel()
        {
            try
            {
                SearchPrintLoginCodeModel search = new SearchPrintLoginCodeModel();

                List<HouseHoldModel> lstExport = Search(search);
                //xuly in excel cho list lstExport
                return File(String.Empty, String.Empty);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        #region In excel
        public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)
        {
            try
            {
                PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
                DataTable table = new DataTable();

                foreach (PropertyDescriptor prop in properties)
                    table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                if (data != null)
                {
                    foreach (T item in data)
                    {
                        DataRow row = table.NewRow();

                        foreach (PropertyDescriptor prop in properties)
                            row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

                        table.Rows.Add(row);
                    }
                }
                return table;
            }
            catch(Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        [HttpGet]
        public FileResult ExportData(int? HouseNumber, string AreaId, string HouseHoldName, string StatusGenerateCode, string StatusPrint)
        {
            if (HouseHoldName == String.Empty || HouseHoldName == "undefined")
                HouseHoldName = null;
            if (AreaId == String.Empty || AreaId == "undefined")
                AreaId = null;
            if (StatusGenerateCode == String.Empty || StatusGenerateCode == "undefined")
                StatusGenerateCode = null;
            if (StatusPrint == String.Empty || StatusPrint == "undefined")
                StatusPrint = null;

            try
            {
                SearchPrintLoginCodeModel search = new SearchPrintLoginCodeModel();
                string strAdminCode = MyUser.AdminCode;
                search.CommuneId = strAdminCode;
                search.AreaId = AreaId;
                search.StatusGenerateCode = StatusGenerateCode;
                search.StatusPrint = StatusPrint;
                search.HouseHoldName = HouseHoldName;
                search.HouseNumber = HouseNumber;
                //set pageszie and pagint
                search.PageIndex = 1;
                search.PageSize = 10000;
                string TenDB = MyUser.AdminName;

                List<HouseHoldModel> lstHouseHold = Search(search);
                List<printloginExcel> householdListExcel = new List<printloginExcel>();
                foreach (HouseHoldModel h in lstHouseHold)
                {
                    try
                    {
                        householdListExcel.Add(new printloginExcel()
                        {
                            HOUSEHOLD_NAME = h.HOUSEHOLD_NAME,
                            AREA_ID_NAME = h.AREA_ID_NAME,
                            ADDRESS_ID_NAME = h.ADDRESS_ID_NAME,
                            NUM_OF_HH_MEMBER = h.NUM_OF_HH_MEMBER,
                            HH_HEAD_MOBILE = h.HH_HEAD_MOBILE,
                            HH_HEAD_EMAIL = h.HH_HEAD_EMAIL,
                            LOGINCODE = h.LOGINCODE
                        });
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex);
                        throw;
                        //throw;
                    }
                }

                //in ra file excel
                DataTable dt = ConvertToDataTable(householdListExcel);
                //ExportExcel(dt,"HaNoi");
                //string TenDB = "HaNoi";
                using (var excelPackage = new ExcelPackage())
                {
                    excelPackage.Workbook.Properties.Author = "MR. Quy";
                    excelPackage.Workbook.Properties.Title = "Export excel";
                    var sheet = excelPackage.Workbook.Worksheets.Add("Print Login");
                    // output a line for the headers
                    //CreateHeader(sheet);
                    sheet.Name = "ThongTinLogin";
                    // all indexes start at 1
                    sheet.Cells["A1"].Value = "Thông tin tài khoản";
                    sheet.Cells["A1:H1"].Merge = true;
                    sheet.Cells["A1:H1"].Style.Font.Size = 18;
                    sheet.Cells["A1:H1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells["A1:H1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells["A1:H1"].Style.Font.Bold = true;
                    sheet.Cells["A1:H1"].Style.WrapText = true;

                    DataTable table = new DataTable();
                    table.Columns.Add("STT", typeof(string));

                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        DataRow row = table.NewRow();
                        row["STT"] = i;
                        table.Rows.Add(row);
                    }

                    sheet.Cells["A2"].Value = "(" + MyUser.AdminName + ")";
                    sheet.Cells["A2:H2"].Merge = true;
                    sheet.Cells["A2:H2"].Style.Font.Size = 10;
                    sheet.Cells["A2:H2"].Style.Font.Color.SetColor(Color.Red);
                    sheet.Cells["A2:H2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells["A2:H2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells["A2:H2"].Style.Font.Bold = true;
                    sheet.Cells["A2:H2"].Style.WrapText = true;

                    #region Temp - vanduong
                    //sheet.Cells["A4"].Value = "STT";
                    //sheet.Cells["B4"].Value = "Tên user";
                    //sheet.Cells["C4"].Value = "Địa bàn User";
                    //sheet.Cells["D4"].Value = "Địa chỉ user";
                    //sheet.Cells["E4"].Value = "Số nhân khẩu";
                    //sheet.Cells["F4"].Value = "SĐT user";
                    //sheet.Cells["G4"].Value = "Email user";
                    //sheet.Cells["H4"].Value = "Mã đăng nhập"; 
                    #endregion

                    sheet.Cells["A4"].Value = "STT";
                    sheet.Cells["B4"].Value = "Họ tên";
                    sheet.Cells["C4"].Value = "Địa bàn";
                    sheet.Cells["D4"].Value = "Địa chỉ";
                    sheet.Cells["E4"].Value = "Số nhân khẩu";
                    sheet.Cells["F4"].Value = "Số ĐT";
                    sheet.Cells["G4"].Value = "Email";
                    sheet.Cells["H4"].Value = "Mã đăng nhập";
                    sheet.Cells["A4:H4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    using (var rng = sheet.Cells["A3:I3"])
                    {
                        rng.Style.Font.Bold = true;
                        rng.Style.WrapText = true;
                        rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        rng.Style.Font.Size = 12;
                    }

                    sheet.Cells["B5"].LoadFromDataTable(dt, true);
                    sheet.Cells["A5"].LoadFromDataTable(table, true);
                    sheet.DeleteRow(5);

                    for (var j = 0; j < dt.Columns.Count + 1; j++)
                    {
                        for (var i = 0; i <= dt.Rows.Count; i++)
                        {
                            sheet.Cells[i + 4, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        }
                    }

                    sheet.Cells["A1:AN"].AutoFitColumns();
                    sheet.Cells["B1:BN"].AutoFitColumns();
                    sheet.Cells["C1:CN"].AutoFitColumns();
                    sheet.Cells["D1:DN"].AutoFitColumns();
                    sheet.Cells["E1:EN"].AutoFitColumns();
                    sheet.Cells["F1:FN"].AutoFitColumns();
                    sheet.Cells["G1:GN"].AutoFitColumns();
                    sheet.Cells["H1:HN"].AutoFitColumns();

                    //sheet.Column(3).Style.Numberformat.Format = "$#,##0.00";
                    //sheet.Column(2).Style.Numberformat.Format = "$#,##0.00";
                    sheet.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    sheet.Column(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(4).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(5).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(6).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(7).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(8).Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    using (MemoryStream stream = new MemoryStream())
                    {
                        ZipOutputStream zipstream = new ZipOutputStream(stream);
                        zipstream.SetLevel(3); //0-9, 9 being the highest level of compression


                        // loops through the PDFs I need to create
                        var newEntry = new ZipEntry("PrintLogin.xlsx");
                        newEntry.DateTime = DateTime.Now;
                        zipstream.PutNextEntry(newEntry);

                        using (MemoryStream inStream = new MemoryStream(excelPackage.GetAsByteArray()))
                        {
                            StreamUtils.Copy(inStream, zipstream, new byte[4096]);
                            inStream.Close();
                            zipstream.CloseEntry();

                            zipstream.IsStreamOwner = false;    // False stops the Close also Closing the underlying stream.
                            zipstream.Close();          // Must finish the ZipOutputStream before using outputMemStream.
                            stream.Position = 0;

                            LogStream.Write<List<HouseHoldModel>>(MyUser.UserName,
                              CommonData.AppName,
                              "In mã đăng nhập - PrintLoginCodeController",
                              "Xuất mã ra excell- ExportData",
                              lstHouseHold);
                        }

                        return File(stream.ToArray(), "application/octet-stream", "DSPrintLogin-" + TenDB + ".zip");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw ;
            }
        }
        #endregion

    }
}